﻿using System;
using System.Collections.Generic;
using System.Data;
using Dapper;
using Dapper.Contrib.Extensions;
using System.Linq;
using System.Data.SqlClient;
using System.Linq.Expressions;

namespace LuDragon.Data.DataHandler
{
    public class DapperDataServer
    {

        #region WriteDB

        /// <summary>
        /// 添加数据信息
        /// </summary>
        /// <param name="model">实体</param>
        /// <param name="dbWrite">写数据库连接</param>
        /// <param name="transaction">事物</param>
        /// <returns></returns>
        public void Add<TModel>(TModel model, IDbConnection dbWrite = null, IDbTransaction transaction = null) where TModel : class
        {
            if (model == null) throw new ArgumentNullException();

            if (dbWrite == null)
            {
                dbWrite = WriteConnectionPool.Pool.Connection;
                dbWrite.Insert(model);
                WriteConnectionPool.Pool.CloseConnection(dbWrite);
            }
            else
            {
                dbWrite.Insert(model, transaction);
            }
        }

        /// <summary>
        /// 批量添加数据信息
        /// </summary>
        /// <param name="list">实体集合</param>
        /// <param name="dbWrite">写数据库连接</param>
        /// <param name="transaction">事物</param>
        /// <returns></returns>
        public void Add<TModel>(IEnumerable<TModel> list, IDbConnection dbWrite = null, IDbTransaction transaction = null) where TModel : class
        {
            if (!list.Any()) throw new ArgumentNullException();

            if (dbWrite == null)
            {
                dbWrite = WriteConnectionPool.Pool.Connection;
                dbWrite.Insert(list);
                WriteConnectionPool.Pool.CloseConnection(dbWrite);
            }
            else
            {
                dbWrite.Insert(list, transaction);
            }
        }

        /// <summary>
        /// 更新数据信息
        /// </summary>
        /// <param name="model">实体</param>
        /// <param name="dbWrite">写数据库连接</param>
        /// <param name="transaction">事物</param>
        /// <returns></returns>
        public void Update<TModel>(TModel model, IDbConnection dbWrite = null, IDbTransaction transaction = null) where TModel : class
        {
            if (model == null) throw new ArgumentNullException();

            if (dbWrite == null)
            {
                dbWrite = WriteConnectionPool.Pool.Connection;
                dbWrite.Update(model);
                WriteConnectionPool.Pool.CloseConnection(dbWrite);
            }
            else
            {
                dbWrite.Update(model, transaction);
            }
        }
        /// <summary>
        /// 批量更新数据信息
        /// </summary>
        /// <param name="list">实体集合</param>
        /// <param name="dbWrite">写数据库连接</param>
        /// <param name="transaction">事物</param>
        /// <returns></returns>
        public void Update<TModel>(IEnumerable<TModel> list, IDbConnection dbWrite = null, IDbTransaction transaction = null) where TModel : class
        {
            if (!list.Any()) throw new ArgumentNullException();

            if (dbWrite == null)
            {
                dbWrite = WriteConnectionPool.Pool.Connection;
                dbWrite.Update(list);
                WriteConnectionPool.Pool.CloseConnection(dbWrite);
            }
            else
            {
                dbWrite.Update(list, transaction);
            }
        }

        /// <summary>
        /// 删除数据信息
        /// </summary>
        /// <param name="model">实体</param>
        /// <param name="dbWrite">写数据库连接</param>
        /// <param name="transaction">事物</param>
        /// <returns></returns>
        public void Delete<TModel>(TModel model, IDbConnection dbWrite = null, IDbTransaction transaction = null) where TModel : class
        {
            if (model == null) throw new ArgumentNullException();

            if (dbWrite == null)
            {
                dbWrite = WriteConnectionPool.Pool.Connection;
                dbWrite.Delete(model);
                WriteConnectionPool.Pool.CloseConnection(dbWrite);
            }
            else
            {
                dbWrite.Delete(model, transaction);
            }
        }

        /// <summary>
        /// 批量删除数据信息
        /// </summary>
        /// <param name="list">实体集合</param>
        /// <param name="dbWrite">写数据库连接</param>
        /// <param name="transaction">事物</param>
        /// <returns></returns>
        public void Delete<TModel>(IEnumerable<TModel> list, IDbConnection dbWrite = null, IDbTransaction transaction = null) where TModel : class
        {
            if (!list.Any()) throw new ArgumentNullException();
            if (dbWrite == null)
            {
                dbWrite = WriteConnectionPool.Pool.Connection;
                dbWrite.Delete(list);
                WriteConnectionPool.Pool.CloseConnection(dbWrite);
            }
            else
            {
                dbWrite.Delete(list, transaction);
            }
        }

        /// <summary>
        /// 执行sql语句
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="dbWrite">写数据库连接</param>
        /// <param name="transaction">事物</param>
        /// <returns></returns>
        public int Execute(string sql, IDbConnection dbWrite = null, IDbTransaction transaction = null)
        {
            int result;
            if (dbWrite == null)
            {
                dbWrite = WriteConnectionPool.Pool.Connection;
                result = dbWrite.Execute(sql);
                WriteConnectionPool.Pool.CloseConnection(dbWrite);
                return result;
            }
            //dbWrite.CreateCommand().Transaction = transaction;
            //result= dbWrite.Execute(sql);
            IDbCommand command = dbWrite.CreateCommand();
            command.Transaction = transaction;
            command.CommandText = sql;
            result = command.ExecuteNonQuery();
            return result;
        }

        #endregion

        #region ReadDB

        /// <summary>
        /// 获取实体数据信息
        /// </summary>
        /// <param name="id">主键</param>
        /// <returns></returns>
        public TModel GetModel<TModel>(object id) where TModel : class
        {
            var dbRead = ReadConnectionPool.Pool.Connection;
            var result = dbRead.Get<TModel>(id);
            ReadConnectionPool.Pool.CloseConnection(dbRead);
            return result;
        }
        /// <summary>
        /// 获取实体所有数据信息列表
        /// </summary>
        /// <returns></returns>
        public IEnumerable<TModel> GetAll<TModel>() where TModel : class
        {
            var dbRead = ReadConnectionPool.Pool.Connection;
            var result = dbRead.GetAll<TModel>();
            ReadConnectionPool.Pool.CloseConnection(dbRead);
            return result;
        }

        /// <summary>
        /// 查询数据列表
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <returns></returns>
        public IEnumerable<T> Query<T>(string sql, object param = null, CommandType? commandType = null)
        {
            var dbRead = ReadConnectionPool.Pool.Connection;

            var result = dbRead.Query<T>(sql, param, null, true, null, commandType);

            ReadConnectionPool.Pool.CloseConnection(dbRead);
            return result;
        }
        /// <summary>
        /// sql语句查询 
        /// </summary>
        /// <param name="con">数据库连接字符串</param>
        /// <param name="sql">Sql语句</param>
        /// <returns>返回数据集DataSet</returns>
        public DataSet QueryBySql(string sql, SqlParameter[] parameters = null, string connectionString = null)
        {
            if (string.IsNullOrWhiteSpace(connectionString))
            {
                connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ReadDbContext"]?.ToString();
            }
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                try
                {
                    SqlCommand cmd = new SqlCommand
                    {
                        CommandText = sql,
                        Connection = conn,
                        CommandType = CommandType.Text
                    };
                    conn.Open();
                    if (parameters != null)
                    {
                        cmd.Parameters.AddRange(parameters);
                    }
                    SqlDataAdapter da = new SqlDataAdapter { SelectCommand = cmd };
                    DataSet ds = new DataSet();
                    da.Fill(ds);
                    conn.Close();
                    return ds;
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.Message, ex);
                }
            }
        }
        #endregion

    }
}